"""
Case Type   : GUC
Case Name   : enable_auto_clean_unique_sql为on自动淘汰Last_upadated时间较旧的SQL
Create At   : 2024-4-10
Owner       : @peilinqian
Description :
    step1：设置相关sql信息收集功能参数；
    step2：连接数据库，查询统计SQL相关信息；
    step3：连接数据库，执行20条不同sql；
    step4：连接数据库，查询统计SQL相关统计信息至中间表（以防后台sql进行干扰）；
    step5：相关统计sql按Last_upadated倒序排序与执行sql倒序结果比对
Expect      :
    step1：设置相关sql信息收集功能参数；expect：成功
    step2：连接数据库，查询统计SQL相关信息；expect：成功
    step3：连接数据库，执行20条不同sql；expect：成功
    step4：连接数据库，查询统计SQL相关统计信息至中间表（以防后台sql进行干扰）；expect：成功
    step5：相关统计sql按Last_upadated倒序排序与执行sql倒序结果比对；expect：结果一致
History     :
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class GUCQuery(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}: start----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_oguser = Node(node='PrimaryDbUser')
        self.common = Common()
        self.constant = Constant()
        self.t_name = "t_guc_query_case0050"
        self.t_tmp = "t_guc_query_case0050_tmp"

    def test_main(self):
        step_txt = '----查询初始参数值----'
        self.log.info(step_txt)
        self.init_para1 = self.common.show_param('enable_resource_track')
        self.init_para2 = self.common.show_param('enable_auto_clean_unique_sql')
        self.init_para3 = self.common.show_param('instr_unique_sql_count')

        step_txt = '----step1：设置相关sql信息收集功能参数；expect：成功----'
        self.log.info(step_txt)
        if self.init_para2 != 'on':
            result = self.pri_sh.execute_gsguc("set",
                                               self.constant.GSGUC_SUCCESS_MSG,
                                               "enable_auto_clean_unique_sql=on",
                                               single=True)
            self.assertTrue(result, '执行失败:' + step_txt)
            restart_result = self.pri_sh.restart_db_cluster()
            self.assertTrue(restart_result, '执行失败:' + step_txt)
        result = self.pri_sh.execute_gsguc("reload",
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           "enable_resource_track=on",
                                           single=True)
        self.assertTrue(result, '执行失败:' + step_txt)
        result = self.pri_sh.execute_gsguc("reload",
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           "instr_unique_sql_count=10",
                                           single=True)
        self.assertTrue(result, '执行失败:' + step_txt)

        step_txt = '----step2：连接数据库，查询统计SQL相关信息；expect：成功----'
        self.log.info(step_txt)
        check_sql = f'select last_updated,substring(query,0,100),n_calls ' \
                    f'from dbe_perf.statement order by last_updated desc;'
        check_result = self.pri_sh.execut_db_sql(check_sql)
        self.log.info(check_result)

        step_txt = '----step3：连接数据库，执行20条不同sql；expect：成功----'
        self.log.info(step_txt)
        for i in range(20):
            create_sql = f'create table {self.t_name}_{i}(id int,name text);'
            create_result = self.pri_sh.execut_db_sql(create_sql)
            self.log.info(create_result)
            self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_result,
                          '执行失败:' + step_txt)

        step_txt = '----step4：连接数据库，查询统计SQL相关统计信息至中间表（以防后台sql进行干扰）；' \
                   'expect：成功----'
        self.log.info(step_txt)
        check_sql = f"select last_updated,substring(query,0,100),n_calls " \
                    f"into {self.t_tmp} from dbe_perf.statement where query " \
                    f"like '%{self.t_name}%';"
        check_result = self.pri_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, check_result,
                      '执行失败:' + step_txt)

        step_txt = '----step5：相关统计sql按Last_upadated倒序排序与执行sql倒序结果比对；' \
                   'expect：结果一致----'
        self.log.info(step_txt)
        check_sql1 = f"select * from {self.t_tmp} order by last_updated desc"
        check_result1 = self.pri_sh.execut_db_sql(check_sql1)
        self.log.info(check_result1)
        check_sql2 = f"select * from {self.t_tmp} order by substring desc"
        check_result2 = self.pri_sh.execut_db_sql(check_sql2)
        self.log.info(check_result2)
        self.assertEqual(check_result2, check_result1, '执行失败:' + step_txt)

    def tearDown(self):
        step6_txt = '----step6: 还原参数，清理表----'
        self.log.info(step6_txt)
        if self.init_para2 != 'on':
            self.result1 = self.pri_sh.execute_gsguc(
                "set",
                self.constant.GSGUC_SUCCESS_MSG,
                f"enable_auto_clean_unique_sql="
                f"{self.init_para2}",
                single=True)
            self.restart_result = self.pri_sh.restart_db_cluster()
        result2 = self.pri_sh.execute_gsguc("reload",
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            f"enable_resource_track="
                                            f"{self.init_para1}",
                                            single=True)
        result3 = self.pri_sh.execute_gsguc("reload",
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            f"instr_unique_sql_count="
                                            f"{self.init_para3}",
                                            single=True)
        self.drop_results = []
        for i in range(20):
            drop_sql = f'drop table if exists {self.t_name}_{i};'
            drop_result = self.pri_sh.execut_db_sql(drop_sql)
            self.log.info(drop_result)
            self.drop_results.append(drop_result)
        drop_sql = f'drop table if exists {self.t_tmp};'
        drop_result = self.pri_sh.execut_db_sql(drop_sql)
        self.log.info(drop_result)
        self.drop_results.append(drop_result)

        self.log.info(f'----{os.path.basename(__file__)}: end----')
        if self.init_para2 != 'on':
            self.assertTrue(self.result1, '执行失败:' + step6_txt)
            self.assertTrue(self.restart_result, '执行失败:' + step6_txt)
        self.assertTrue(result2, '执行失败:' + step6_txt)
        self.assertTrue(result3, '执行失败:' + step6_txt)
        self.assertEqual(self.drop_results.count(
            self.constant.TABLE_DROP_SUCCESS), 21, '执行失败:' + step6_txt)
